﻿<cfsilent>

	<cfflush interval="100" />
	
	<cfscript>

		temFile = GetTempDirectory() & createUUID() & ".xls";
		bookUpdate = 0;
		bookInsert = 0;
		
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		strAdvice = getProperty("serviceFactory").getBean("stringAdvice");

		sequenceBookPublisher = getProperty("serviceFactory").getBean("sequenceBookPublisher");
		sequenceBook = getProperty("serviceFactory").getBean("sequenceBook");

		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labImport", true);
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入教材数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="教材基础信息">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入教材信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->						

				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "ISBN")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "出版社")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "书名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "版次")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "作者")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "定价")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "获奖项目")><cfset columnChecked = false /></cfif>
				
				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: ISBN, 出版社, 书名, 版次, 作者, 定价, 获奖项目</p>
				</cfif>
				
				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
					
					<cftry>
					
						<cftransaction>
						
							<!--- 检查出版社是否已预先建立 --->
							<cfset pubArray = [] />
							<cfloop query="rs">
								<cfset newPuber = strAdvice.trimExcel( rs['出版社'][rs.currentRow] ) />
								
								<cfif not arrayFind(pubArray, newPuber) and len(newPuber)>
									<cfset arrayAppend(pubArray, newPuber) />
								</cfif>
							</cfloop>
						
							<cfloop array="#pubArray#" index="pubName">
							
                                <cfset sql = "	SELECT
													p.pid,p.publisher
												FROM
													t_publisher p
												WHERE
													p.publisher = :pubName " />

                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
                                <cfset queryObj.addParam(name = "pubName", value = pubName, cfsqltype = "cf_sql_varchar") />
                                <cfset rs_publisher = queryObj.execute(sql = sql).getResult()/>
							
								<cfif rs_publisher.recordCount eq 0>
									
									<!--- 生成拼音索检信息 --->
									<cfset py = "" />
									<cfloop from="1" to="#len(pubName)#" index="i">
										<cfset char = mid(pubName, i, 1) />
										<cfif refind("^[A-Za-z0-9]+$", char)>
												<cfset py = py & char />
											<cfelse>
                                                <cfscript>
                                                	sql = "	SELECT
																c.charidx
															FROM
																t_chinese c
															WHERE
																c.character = :char ";
													
													queryObj = new Query(datasource = application.dnsMaster);
													queryObj.addParam(name = "char", value = char, cfsqltype = "cf_sql_char");
													rs_checkQuery = queryObj.execute(sql = sql).getResult();
								
													if (rs_checkQuery.recordCount) {
														py = py & rs_checkQuery.charidx;
													}
                                                </cfscript>
										</cfif>
									</cfloop>
									
                                    <cfset sql = "	INSERT INTO t_publisher(
														pid,
														publisher,
														book_count,
														pyidx,
														pyid
													)VALUES(
														:pubId,
														:pubName,
														:bookCount,
														:pyIndex,
														:pyFirst 
													)" />
									
									<cfset queryObj = new Query(datasource = application.dnsMaster) />
									<cfset queryObj.addParam(name = "pubId", value = sequenceBookPublisher.getID(), cfsqltype = "cf_sql_char") />
                                    <cfset queryObj.addParam(name = "pubName", value = pubName, cfsqltype = "cf_sql_varchar") />
                                    <cfset queryObj.addParam(name = "bookCount", value = 0, cfsqltype = "cf_sql_integer") />
                                    <cfset queryObj.addParam(name = "pyIndex", value = py, cfsqltype = "cf_sql_varchar") />
                                    <cfset queryObj.addParam(name = "pyFirst", value = left(py, 1), cfsqltype = "cf_sql_char") />
                                    <cfset queryObj.execute(sql = sql)/>
                                    
                                    <cftransaction action="commit"/>
                                    
								</cfif>
							
							</cfloop>
						
							<cfloop query="rs">
								
								<cfset bookISBN = strAdvice.trimExcel( rs['ISBN'][rs.currentRow] ) />
								<cfset bookISBN = replace( bookISBN, "-", "", "ALL") />
								
								<!--- 检查ISBN --->
								<cfif mathAdvice.isISBN( bookISBN ) >
									
										<!--- /* 处理书名 */ --->
										<cfset bookName = strAdvice.trimExcel( rs['书名'][rs.currentRow] ) />
										<cfset bookName = replace(bookName, "（", "(", "ALL") />
										<cfset bookName = replace(bookName, "）", ")", "ALL") />
										<cfset bookName = replace(bookName, "《", "", "ALL") />
										<cfset bookName = replace(bookName, "》", "", "ALL") />
										<cfset bookName = replace(bookName, "•", ".", "ALL") />
										
										<!--- /* 处理作者分割问题 */ --->
										
										<cfset autherList = strAdvice.trimExcel( rs['作者'][rs.currentRow] ) />
										<cfset autherList = replace(autherList, "•", ".", "ALL") />
										<cfset autherList = replace(autherList, " 译", "", "ALL") />
										<cfset autherList = replace(autherList, " 等", "", "ALL") />
										<cfset autherList = replace(autherList, " 等译", "", "ALL") />
										<cfset autherList = replace(autherList, " 著", "", "ALL") />
										
										<cfset auther = listToArray( autherList, "、，, ") />
										<cfset bookAuthors = arrayToList(auther, "/") />
										
										<!--- /* 处理版次写法 */ --->
										<cfset bookEdtion = arrayToList(listToArray( strAdvice.trimExcel( rs['版次'][rs.currentRow] ), ",.-"), ".") />
										<cfif isNumeric(bookEdtion)>
												<cfif listLen(bookEdtion, ".") eq 1>
													<cfset bookEdtion = "YYYY." & bookEdtion />
												</cfif>
											<cfelse>
												<cfset bookEdtion = "YYYY.1" />
										</cfif>
										<!--- /* 处理价格 */ --->
										<cfset bookPrise = rs['定价'][rs.currentRow] />
										<cfset bookPrise = replace(bookPrise, "（", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "）", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "(", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, ")", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "*", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "估", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "￥", "", "ALL") />
										<cfset bookPrise = replace(bookPrise, "¥", "", "ALL") />
										<cfset bookPrise = strAdvice.trimExcel(bookPrise) />
									
										<!--- 检查书名 --->
										<cfif len(bookName) >
												<!--- 检查作者 --->
												<cfif len(bookAuthors) >
													
														<!--- 插入或更新教材信息 --->
                                                        <cfset sql = "	SELECT
																			b.bid
																		FROM
																			t_book b
																		WHERE
																			b.book_isbn = :bookISBN " />
														
                                                        <cfset queryObj = new Query(datasource = application.dnsMaster) />
														<cfset queryObj.addParam(name = "bookISBN", value = bookISBN, cfsqltype = "cf_sql_varchar") />
                                                        <cfset rs_bookEntity = queryObj.execute(sql = sql).getResult()/>

                                                        <cfset bookEdition = bookEdtion />

														<cfif rs_bookEntity.recordCount eq 0 >
																<!--- 创建新教材 --->
																<cfset bookInsert++ />
                                                                
                                                                <!--- 生成拼音索检信息 --->
                                                                <cfset py = "" />
                                                                <cfloop from="1" to="#len(bookName)#" index="i">
                                                                    <cfset char = mid(bookName, i, 1) />
                                                                    <cfif refind("^[A-Za-z0-9]+$", char)>
                                                                            <cfset py = py & char />
                                                                        <cfelse>
                                                                            <cfscript>
																				sql = "	SELECT
																							c.charidx
																						FROM
																							t_chinese c
																						WHERE
																							c.character = :char ";
																				
																				queryObj = new Query(datasource = application.dnsMaster);
																				queryObj.addParam(name = "char", value = char, cfsqltype = "cf_sql_char");
																				rs_checkQuery = queryObj.execute(sql = sql).getResult();
															
																				if (rs_checkQuery.recordCount) {
																					py = py & rs_checkQuery.charidx;
																				}
																			</cfscript>
                                                                    </cfif>
                                                                </cfloop>
                                                            	
                                                                <cfset pyIndex = py />
                                                                <cfset pyFirst = left(py,1) />
																
                                                                <cfset sql = "	SELECT
																					p.pid,p.publisher
																				FROM
																					t_publisher p
																				WHERE
																					p.publisher = :pubName " />
                                                                
                                                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
                                                                <cfset queryObj.addParam(name = "pubName", value = strAdvice.trimExcel( rs['出版社'][rs.currentRow] ), cfsqltype = "cf_sql_varchar") />
                                                                <cfset rs_publisherEntity = queryObj.execute(sql = sql).getResult() />
                                                                
                                                                <cfset sql = "	INSERT INTO t_book (
																					bid,
																					pid,
																					book_name,
																					book_authors,
																					book_edition,
																					book_isbn,
																					book_awards,
																					book_prise,
																					book_stock,
																					pyidx,
																					pyid
																				)VALUES(
																					:bookId,
																					:pubId,
																					:bookName,
																					:bookAuthors,
																					:bookEdition,
																					:bookISBN,
																					:bookAwards,
																					:bookPrise,
																					:bookStock,
																					:pyIndex,
																					:pyFirst 
																				)" />
                                                                
                                                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
																<cfset queryObj.addParam(name = "bookId", value = sequenceBook.getID(), cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "pubId", value = rs_publisherEntity.pid, cfsqltype = "cf_sql_char") />
                                                                <cfset queryObj.addParam(name = "bookName", value = bookName, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookAuthors", value = bookAuthors, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookEdition", value = bookEdition, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookISBN", value = bookISBN, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookAwards", value = len( rs['获奖项目'][rs.currentRow] ) ? rs['获奖项目'][rs.currentRow] : "", cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookPrise", value = isNumeric( bookPrise )? bookPrise : 0, cfsqltype = "cf_sql_decimal", scale = 2) />
                                                                <cfset queryObj.addParam(name = "bookStock", value = 0, cfsqltype = "cf_sql_integer") />
                                                                <cfset queryObj.addParam(name = "pyIndex", value = py, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "pyFirst", value = left(py, 1), cfsqltype = "cf_sql_char") />
                                                                <cfset queryObj.execute(sql = sql)/>
                                                                
                                                                <cftransaction action="commit"/>
                                                                
                                                         	<cfelse>
                                                                <!--- 更新教材 --->
                                                                <cfset bookId = rs_bookEntity.bid />

                                                                <cfset bookEdition = bookEdtion />

                                                                <!--- 生成拼音索检信息 --->
                                                                <cfset py = "" />
                                                                <cfloop from="1" to="#len(bookName)#" index="i">
                                                                    <cfset char = mid(bookName, i, 1) />
                                                                    <cfif refind("^[A-Za-z0-9]+$", char)>
                                                                            <cfset py = py & char />
                                                                        <cfelse>
                                                                            <cfscript>
																				sql = "	SELECT
																							c.charidx
																						FROM
																							t_chinese c
																						WHERE
																							c.character = :char ";
																				
																				queryObj = new Query(datasource = application.dnsMaster);
																				queryObj.addParam(name = "char", value = char, cfsqltype = "cf_sql_char");
																				rs_checkQuery = queryObj.execute(sql = sql).getResult();
															
																				if (rs_checkQuery.recordCount) {
																					py = py & rs_checkQuery.charidx;
																				}
																			</cfscript>
                                                                    </cfif>
                                                                </cfloop>
                                                            	
                                                                <cfset sql = "	SELECT
																					p.pid,p.publisher
																				FROM
																					t_publisher p
																				WHERE
																					p.publisher = :pubName " />
                                                                
                                                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
                                                                <cfset queryObj.addParam(name = "pubName", value = strAdvice.trimExcel( rs['出版社'][rs.currentRow] ), cfsqltype = "cf_sql_varchar") />
                                                                <cfset rs_publisherEntity = queryObj.execute(sql = sql).getResult() />
                                                                
                                                                <cfset sql = "	UPDATE 
																					t_book b
																				SET
																					b.book_edition = :bookEdition,
																					b.book_name = :bookName,
																					b.pyidx = :pyIndex,
																					b.pyid = :pyFirst,
																					b.book_awards = :bookAwards,
																					b.book_prise = :bookPrise,
																					b.pid = :pubId 
																				WHERE
																					b.bid = :bookId " />
                                                                
                                                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
																<cfset queryObj.addParam(name = "bookId", value = bookId, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "pubId", value = rs_publisherEntity.pid, cfsqltype = "cf_sql_char") />
                                                                <cfset queryObj.addParam(name = "bookName", value = bookName, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookAuthors", value = bookAuthors, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookEdition", value = bookEdition, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookAwards", value = len( rs['获奖项目'][rs.currentRow] ) ? rs['获奖项目'][rs.currentRow] : "", cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "bookPrise", value = isNumeric( bookPrise )?bookPrise:0, cfsqltype = "cf_sql_decimal", scale = 2) />
                                                                <cfset queryObj.addParam(name = "pyIndex", value = py, cfsqltype = "cf_sql_varchar") />
                                                                <cfset queryObj.addParam(name = "pyFirst", value = left(py, 1), cfsqltype = "cf_sql_char") />
                                                                <cfset queryObj.execute(sql = sql)/>
                                                                
                                                                <cftransaction action="commit"/>
                                                                
																<cfset bookUpdate++ />
														</cfif>
													
														
																							
													<cfelse>
														<p class="error">教材 <cfoutput>#rs['书名'][rs.currentRow]#</cfoutput>(<cfoutput>#rs['出版社'][rs.currentRow]#</cfoutput>) ISBN: <cfoutput>#rs['ISBN'][rs.currentRow]#</cfoutput> 作者为空</p>
												</cfif>
											<cfelse>
												<p class="error">教材 ~<cfoutput>#rs['作者'][rs.currentRow]#</cfoutput>(<cfoutput>#rs['出版社'][rs.currentRow]#</cfoutput>) ISBN: <cfoutput>#rs['ISBN'][rs.currentRow]#</cfoutput> 书名为空</p>
										</cfif>
									
									<cfelse>
										<p class="error">教材 <cfoutput>#rs['书名'][rs.currentRow]#</cfoutput>~<cfoutput>#rs['作者'][rs.currentRow]#</cfoutput>(<cfoutput>#rs['出版社'][rs.currentRow]#</cfoutput>) ISBN: <cfoutput>#rs['ISBN'][rs.currentRow]#</cfoutput> 出版号不正确</p>
								</cfif>
							
							</cfloop>
						
							<!--- 刷新出版社注册图书数量 --->
                            <cfset sql = "	SELECT
												b.pid,COUNT(*) b_count
											FROM
												t_book b
											GROUP BY
												b.pid" />
                            
                            <cfset queryObj = new Query(datasource = application.dnsMaster) />
                            <cfset rs_publishBookCount = queryObj.execute(sql = sql).getResult() />
                            
                            <cfloop query="rs_publishBookCount">

                            	<cfset sql = 	"UPDATE
													t_publisher p
												SET
													p.book_count = :bookCount 
												WHERE
													p.pid = :pubId " />
                            	
                                <cfset queryObj = new Query(datasource = application.dnsMaster) />
                                <cfset queryObj.addParam(name = "bookCount", value = rs_publishBookCount.b_count, cfsqltype = "cf_sql_integer") />
                                <cfset queryObj.addParam(name = "pubId", value = rs_publishBookCount.pid, cfsqltype = "cf_sql_char") />
                                <cfset queryObj.execute(sql = sql)/>

                            </cfloop>
                            
							<p class="success">刷新出版社图书数量 ... 完成</p> 
						
						</cftransaction>

						<cfcatch type="any">
							<p class="error">数据导入错误... <cfoutput>#cfcatch.Message#</cfoutput></p>
                            <cfthrow object="#cfcatch#"/>
						</cfcatch>
					</cftry>
					
				</cfif>
				
			</cfif>
			
			
		</cfif> 
		
		<p>新增教材数据 [<cfoutput>#bookInsert#</cfoutput>] 条, 更新现有教材数据 [<cfoutput>#bookUpdate#</cfoutput>] 条.</p>
		
		<a target="_parent" href="<cfoutput>#buildURL('bookSchema')#</cfoutput>">刷新教材数据</a>
		
		<!---<a target="_parent" href="<cfoutput>#buildURL('programCourse', targetArgs)#</cfoutput>">重新导入课程</a>--->
		
		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>